/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DAO;

import Models.Company;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author Cham to tet
 */
public class CompanyDAO {

    public int addListing(Company company) {
        int result = 0;
        try {
            Connection con = (Connection) DBConnection.myConnection();
            PreparedStatement stm = con.prepareStatement("Insert into Company values(?,?,?,?,?,?,?,?,?)");
            stm.setString(1, company.getName());
            stm.setString(2, company.getContactPerson());
            stm.setString(3, company.getDesignation());
            stm.setString(4, company.getAddress());
            stm.setString(5, company.getMobile());
            stm.setString(6, company.getTelephone());
            stm.setString(7, company.getFax());
            stm.setString(8, company.getMemberType());
            stm.setNull(9, java.sql.Types.BOOLEAN);
            result = stm.executeUpdate();
            con.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return result;
    }

    public int getCurrentID() {
        int id = 0;
        try {
            Connection con = (Connection) DBConnection.myConnection();
            PreparedStatement stm = con.prepareStatement("select MAX(ID) from Company");
            ResultSet rs = stm.executeQuery();

            while (rs.next()) {
                id = rs.getInt(1);
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return id;
    }

    public static List<Company> getAllCompany() throws Exception {
        ResultSet rs = null;
        List listing = new ArrayList();
        Connection con = (Connection) DBConnection.myConnection();
        PreparedStatement stm = con.prepareStatement("Select * from Company where Status is not null");
        rs = stm.executeQuery();
        Company c = null;
        while (rs.next()) {
            c = new Company();
            c.setId(rs.getInt(1));
            c.setName(rs.getString(2));
            c.setContactPerson(rs.getString(3));
            c.setDesignation(rs.getString(4));
            c.setAddress(rs.getString(5));
            c.setMobile(rs.getString(6));
            c.setTelephone(rs.getString(7));
            c.setFax(rs.getString(8));
            c.setMemberType(rs.getString(9));
            c.setStatus(rs.getBoolean(10));
            listing.add(c);
        }
        con.close();
        return listing;
    }

    public static Company getCompanyByID(int id) throws Exception {
        Company c = new Company();
        Connection con = (Connection) DBConnection.myConnection();
        java.sql.Statement stm = con.createStatement();
        ResultSet rs = stm.executeQuery("Select * from Company where ID=" + id);
        while (rs.next()) {
            c.setId(rs.getInt(1));
            c.setName(rs.getString(2));
            c.setContactPerson(rs.getString(3));
            c.setDesignation(rs.getString(4));
            c.setAddress(rs.getString(5));
            c.setMobile(rs.getString(6));
            c.setTelephone(rs.getString(7));
            c.setFax(rs.getString(8));
            c.setMemberType(rs.getString(9));
            c.setStatus(rs.getBoolean(10));
        }
        con.close();
        return c;
    }

    public static List<Company> getCompanyByKeyword(String keyword, String FindWhere) throws Exception {
        List lst = new ArrayList();
        Connection con = (Connection) DBConnection.myConnection();
        java.sql.Statement stm = con.createStatement();
        ResultSet rs = stm.executeQuery("Select * from Company where " + FindWhere + " like '%" + keyword + "%' And Status='true'");
        while (rs.next()) {
            Company c = new Company();
            c.setId(rs.getInt(1));
            c.setName(rs.getString(2));
            c.setContactPerson(rs.getString(3));
            c.setDesignation(rs.getString(4));
            c.setAddress(rs.getString(5));
            c.setMobile(rs.getString(6));
            c.setTelephone(rs.getString(7));
            c.setFax(rs.getString(8));
            c.setMemberType(rs.getString(9));
            c.setStatus(rs.getBoolean(10));
            lst.add(c);
        }
        con.close();
        return lst;
    }

    public void deleteCompany(int id) {
        try {
            Connection con = (Connection) DBConnection.myConnection();
            PreparedStatement st = con.prepareStatement("Delete from Enquiry where CompanyID=" + id);
            st.executeUpdate();
            st = con.prepareStatement("Delete from Payment where CompanyID=" + id);
            st.executeUpdate();
            st = con.prepareStatement("Delete from Account where CompanyID=" + id);
            st.executeUpdate();
            st = con.prepareStatement("Delete from Company where ID=" + id);
            st.executeUpdate();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

    public static void editCompany(Company com) {
        try {
            Connection con = (Connection) DBConnection.myConnection();
            String sql = "update Company set Name =?, ContactPerson=?, Designation=?, [Address]=?, Mobile=?, Telephone=?, Fax=?, [Status]=? where ID=?";
            PreparedStatement st = con.prepareStatement(sql);

            st.setString(1, com.getName());
            st.setString(2, com.getContactPerson());
            st.setString(3, com.getDesignation());
            st.setString(4, com.getAddress());
            st.setString(5, com.getMobile());
            st.setString(6, com.getTelephone());
            st.setString(7, com.getFax());
            st.setBoolean(8, com.isStatus());
            st.setInt(9, com.getId());
            st.executeUpdate();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

    public int statisticCom() {
        int total = 0;
        try {
            Connection con = (Connection) DBConnection.myConnection();
            PreparedStatement st = con.prepareStatement("Select count(*) from Company");
            ResultSet rs = st.executeQuery();
            while (rs.next()) {
                total = rs.getInt(1);
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return total;
    }

    public int getAllCompDisable() {
        int total = 0;
        try {
            Connection con = (Connection) DBConnection.myConnection();
            PreparedStatement st = con.prepareStatement("select count(*) from Company where Status = 'false' ");
            ResultSet rs = st.executeQuery();
            while (rs.next()) {
                total = rs.getInt(1);
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return total;
    }

    public int getAllCompEnable() {
        int total = 0;
        try {
            Connection con = (Connection) DBConnection.myConnection();
            PreparedStatement st = con.prepareStatement("select count(*) from Company where Status = 'true' ");
            ResultSet rs = st.executeQuery();
            while (rs.next()) {
                total = rs.getInt(1);
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return total;
    }
}
